-- STANDARD SCRIPT
-- FIRST SCRIPT IS ADJUSTMENT TO client_npi1, client_npi2
-- SECOND SCRIPT INSERTS DATA IN insurance0_npi_t
--
--



------------------------------------------------
-- UPDATE FIELDS client_npi1,client_npi2 IN temp_batch_insurance0_t TABLE 


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_client_npi_f() RETURNS integer AS $$
DECLARE
  v_status integer;
BEGIN


RAISE NOTICE 'UPDATE FIELDS client_npi1,client_npi2 IN temp_batch_insurance0_t TABLE';

UPDATE temp_batch_insurance0_t
SET
 client_npi1 = (CASE WHEN cust_id in (15,19,42,46,52) 
                          AND 
                          client_npi1 in ('AL','AK','AS','AZ','AR','CA','CO','CT','DE','DC','FM','FL','GA','GU','HI','ID','IL','IN',
                                          'IA','KS','KY','LA','ME','MH','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM',
                                          'NY','NC','ND','MP','OH','OK','OR','PW','PA','PR','RI','SC','SD','TN','TX','UT','VT','VI',
                                          'VA','WA','WV','WI','WY') THEN temp_batch_insurance0_t.client_npi1
                     WHEN cust_id in (15,19,42,46,52)               THEN '1'
                     ELSE                                                temp_batch_insurance0_t.client_npi1
                     END),
 client_npi2 = (CASE WHEN cust_id in (15,19,42,46,52) 
                          AND 
                          client_npi2 in ('AL','AK','AS','AZ','AR','CA','CO','CT','DE','DC','FM','FL','GA','GU','HI','ID','IL','IN',
                                          'IA','KS','KY','LA','ME','MH','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM',
                                          'NY','NC','ND','MP','OH','OK','OR','PW','PA','PR','RI','SC','SD','TN','TX','UT','VT','VI',
                                          'VA','WA','WV','WI','WY') THEN temp_batch_insurance0_t.client_npi2
                     WHEN  cust_id in (15,19,42,46,52)              THEN '1'
                     ELSE                                                temp_batch_insurance0_t.client_npi2
                     END)
;


GET DIAGNOSTICS v_status = ROW_COUNT;

  return v_status;
END;
$$ LANGUAGE plpgsql;






------------------------------------------------
-- FUNCTION TO INSERT NEW ROWS INTO INSURANCE0_NPI TABLE


\set ON_ERROR_STOP 1
CREATE OR REPLACE FUNCTION upd_insurance0_npi_f() RETURNS integer AS $$
DECLARE
  v_status1 integer;
  v_status2 integer;
  v_batch_run_date timestamp;
  v_cust_id integer;
BEGIN

SELECT INTO v_batch_run_date our_run_date from batch_insurance0_t limit 1;
SELECT INTO v_cust_id cust_id from batch_insurance0_t limit 1;

PERFORM ml_upd_batch_status_insurance0_new_f(v_cust_id,v_batch_run_date,1,8,'START INSERT IN insurance0_npi_t');




-- STEP 1 INSERT RECORDS INTO insurance0_npi_t    


RAISE NOTICE 'START INSERT INTO insurance0_npi_t TABLE';
  
  INSERT INTO insurance0_npi_t
  SELECT
     a.cust_id               ,
     a.client_npi1           ,
     0                       ,
     a.our_run_date          ,
     a.npi1                  ,
     a.legacy1               ,
     a.pin1                  ,
     a.tax_id1               ,
     a.entity_name1          ,
     a.provider_state        ,
     NULL                    ,
     a.entity_pay_to_street1 ,
     a.entity_pay_to_city1   ,
     a.entity_pay_to_state1  ,
     a.entity_pay_to_zip1    ,
     a.entity_information1   ,
     a.entity_phone1         ,
     1 as npi_com_ind        ,
     (CASE WHEN a.npi1 = a.npi2 THEN 1 
           WHEN a.npi2 IS NULL  THEN 0
           ELSE                      0 
           END) as npi_gov_ind 
  FROM
    (SELECT distinct on (cust_id,client_npi1) * FROM batch_insurance0_t where npi1 IS NOT NULL) as a
  WHERE NOT EXISTS
  (
   SELECT 1 FROM insurance0_npi_t b 
   WHERE (a.cust_id = b.cust_id AND a.client_npi1 = b.client_npi) 
  )
  ;


GET DIAGNOSTICS v_status1 = ROW_COUNT;

  INSERT INTO insurance0_npi_t
  SELECT
     a.cust_id               ,
     a.client_npi2           ,
     0                       ,
     a.our_run_date          ,
     a.npi2                  ,
     a.legacy2               ,
     a.pin2                  ,
     a.tax_id2               ,
     a.entity_name2          ,
     a.provider_state        ,
     NULL                    ,
     a.entity_pay_to_street2 ,
     a.entity_pay_to_city2   ,
     a.entity_pay_to_state2  ,
     a.entity_pay_to_zip2    ,
     a.entity_information2   ,
     a.entity_phone2         ,
     0 as npi_com_ind        ,
     1 as npi_gov_ind
  FROM
    (SELECT distinct on (cust_id,client_npi2) * FROM batch_insurance0_t WHERE npi2 IS NOT NULL and npi2 <> npi1) as a
  WHERE NOT EXISTS
  (
   SELECT 1 FROM insurance0_npi_t b
   WHERE (a.cust_id = b.cust_id AND a.client_npi2 = b.client_npi)
  )
  ;

GET DIAGNOSTICS v_status2 = ROW_COUNT;

RAISE NOTICE 'DONE INSERT INTO insurance0_npi_t TABLE ';




-- STEP 2 UPDATE flag_new IN insurance0_npi_t 
/*
UPDATE insurance0_npi_t
SET flag_new = 0
FROM
    (SELECT distinct on (npi,provider_state,npi_com_ind) npi,provider_state,npi_com_ind 
     FROM 
        insurance0_npi_t 
     WHERE 
       (flag_new = 0 and npi IS NOT NULL and provider_state <> 'FL' and  provider_state <> 'OH' and  provider_state <> 'WA' ) 
    )as a
  WHERE
    (a.npi = insurance0_npi_t.npi and a.provider_state = insurance0_npi_t.provider_state and a.npi_com_ind = insurance0_npi_t.npi_com_ind);



UPDATE insurance0_npi_t
SET flag_new = 0
FROM
    (SELECT distinct on (npi,legacy,provider_state,npi_com_ind) npi,legacy,provider_state,npi_com_ind
     FROM
        insurance0_npi_t
     WHERE
       (flag_new = 0 and npi IS NOT NULL and legacy IS NOT NULL and (provider_state = 'FL' or  provider_state = 'OH' or  provider_state = 'WA' ))
    )as a
  WHERE
    (a.npi = insurance0_npi_t.npi AND 
     a.legacy = insurance0_npi_t.legacy AND 
     a.provider_state = insurance0_npi_t.provider_state AND
     a.npi_com_ind = insurance0_npi_t.npi_com_ind);
*/



-- STEP 3 UPDATE batch_status_insurance0_t WITH THE NUMBER INSERTED

  UPDATE batch_status_insurance0_t
    SET
      insurance0_npi_inserted = v_status1 + v_status2
    WHERE
      (batch_status_insurance0_t.last_run_date = v_batch_run_date and batch_status_insurance0_t.cust_id = v_cust_id);


PERFORM ml_upd_batch_status_insurance0_new_f(v_cust_id,v_batch_run_date,1,8,'FINISH INSERT IN insurance0_npi_t');



  return (v_status1 + v_status2);
END;
$$ LANGUAGE plpgsql;
